﻿-- TYPE PACKAGE DECLARATION
CREATE OR REPLACE PACKAGE PDTYPES  
AS
    TYPE REF_CURSOR IS REF CURSOR;
END;

-- INTEGRITY PACKAGE DECLARATION
CREATE OR REPLACE PACKAGE INTEGRITYPACKAGE AS
 PROCEDURE INITNESTLEVEL;
 FUNCTION GETNESTLEVEL RETURN NUMBER;
 PROCEDURE NEXTNESTLEVEL;
 PROCEDURE PREVIOUSNESTLEVEL;
 END INTEGRITYPACKAGE;
/

-- INTEGRITY PACKAGE DEFINITION
CREATE OR REPLACE PACKAGE BODY INTEGRITYPACKAGE AS
 NESTLEVEL NUMBER;

-- PROCEDURE TO INITIALIZE THE TRIGGER NEST LEVEL
 PROCEDURE INITNESTLEVEL IS
 BEGIN
 NESTLEVEL := 0;
 END;


-- FUNCTION TO RETURN THE TRIGGER NEST LEVEL
 FUNCTION GETNESTLEVEL RETURN NUMBER IS
 BEGIN
 IF NESTLEVEL IS NULL THEN
     NESTLEVEL := 0;
 END IF;
 RETURN(NESTLEVEL);
 END;

-- PROCEDURE TO INCREASE THE TRIGGER NEST LEVEL
 PROCEDURE NEXTNESTLEVEL IS
 BEGIN
 IF NESTLEVEL IS NULL THEN
     NESTLEVEL := 0;
 END IF;
 NESTLEVEL := NESTLEVEL + 1;
 END;

-- PROCEDURE TO DECREASE THE TRIGGER NEST LEVEL
 PROCEDURE PREVIOUSNESTLEVEL IS
 BEGIN
 NESTLEVEL := NESTLEVEL - 1;
 END;

 END INTEGRITYPACKAGE;
/


DROP TRIGGER TIB_SERVE_BLACK_LIST
/

DROP TRIGGER TIB_SERVE_CONFIG
/

DROP TRIGGER TIB_SERVE_DATA_SOURCE
/

DROP TRIGGER TIB_SERVE_GIS_DICTIONARY
/

DROP TRIGGER TIB_SERVE_INPUT_PARAM
/

DROP TRIGGER TIB_SERVE_LOGIN_LOG
/

DROP TRIGGER TIB_SERVE_MODIFY_LOG
/

DROP TRIGGER TIB_SERVE_NAV_DATE_RANGE
/

DROP TRIGGER TIB_SERVE_NAV_INPUT_PARAM
/

DROP TRIGGER TIB_SERVE_NAV_OUT_PARAM
/

DROP TRIGGER TIB_SERVE_OUTPUT_PARAM
/

DROP TRIGGER TIB_SERVE_RESULT_EXAMPLE
/

DROP TRIGGER TIB_SERVE_SEGMENT
/

DROP TRIGGER TIB_SERVE_USER
/

DROP TRIGGER TIB_SERVE_USER_AUTHORITY
/

DROP TRIGGER TIB_SERVE_WHITE_LIST
/

ALTER TABLE SERVE_CONFIG
   DROP CONSTRAINT FK_SERVE_CO_REFERENCE_SERVE_DA
/

ALTER TABLE SERVE_CONFIG
   DROP CONSTRAINT FK_SERVE_CO_REFERENCE_SERVE_CO
/

ALTER TABLE SERVE_EXTEND_CONFIG
   DROP CONSTRAINT FK_SERVE_CONFIG_REFERENCE
/

ALTER TABLE SERVE_EXTEND_REGISTER
   DROP CONSTRAINT FK_SERVE_REGISTER_REFERENCE
/

ALTER TABLE SERVE_EXTEND_SPACE
   DROP CONSTRAINT FK_SERVE_SPACE_REFERENCE
/

ALTER TABLE SERVE_GIS_DICTIONARY
   DROP CONSTRAINT FK_SERVE_GI_REFERENCE_SERVE_EX
/

ALTER TABLE SERVE_INPUT_PARAM
   DROP CONSTRAINT FK_SERVE_IN_REFERENCE_SERVE_CO
/

ALTER TABLE SERVE_MODIFY_LOG
   DROP CONSTRAINT FK_SERVE_MO_REFERENCE_SERVE_CO
/

ALTER TABLE SERVE_NAV_DATE_RANGE
   DROP CONSTRAINT FK_SERVE_NA_RANGE
/

ALTER TABLE SERVE_NAV_INPUT_PARAM
   DROP CONSTRAINT FK_SERVE_NA_INPUT
/

ALTER TABLE SERVE_NAV_OUT_PARAM
   DROP CONSTRAINT FK_SERVE_NA_OUTPUT
/

ALTER TABLE SERVE_OUTPUT_PARAM
   DROP CONSTRAINT FK_SERVE_OU_REFERENCE_SERVE_CO
/

ALTER TABLE SERVE_RESULT_EXAMPLE
   DROP CONSTRAINT FK_SERVE_RE_REFERENCE_SERVE_CO
/

ALTER TABLE SERVE_SEGMENT
   DROP CONSTRAINT FK_SERVE_SE_REFERENCE_SERVE_CO
/

ALTER TABLE SERVE_USER_AUTHORITY
   DROP CONSTRAINT FK_SERVE_US_REFERENCE_SERVE_CO
/

ALTER TABLE SERVE_USER_AUTHORITY
   DROP CONSTRAINT FK_SERVE_US_REFERENCE_SERVE_US
/

ALTER TABLE SERVE_WHITE_LIST
   DROP CONSTRAINT FK_SERVE_WH_REFERENCE_SERVE_CO
/

DROP TABLE SERVE_BLACK_LIST CASCADE CONSTRAINTS
/

DROP INDEX UK_SERVICE_CODE
/

DROP TABLE SERVE_CONFIG CASCADE CONSTRAINTS
/

DROP TABLE SERVE_DATA_SOURCE CASCADE CONSTRAINTS
/

DROP TABLE SERVE_EXTEND_CONFIG CASCADE CONSTRAINTS
/

DROP TABLE SERVE_EXTEND_REGISTER CASCADE CONSTRAINTS
/

DROP TABLE SERVE_EXTEND_SPACE CASCADE CONSTRAINTS
/

DROP TABLE SERVE_GIS_DICTIONARY CASCADE CONSTRAINTS
/

DROP TABLE SERVE_INPUT_PARAM CASCADE CONSTRAINTS
/

DROP TABLE SERVE_LOGIN_LOG CASCADE CONSTRAINTS
/

DROP TABLE SERVE_MODIFY_LOG CASCADE CONSTRAINTS
/

DROP TABLE SERVE_NAV_DATE_RANGE CASCADE CONSTRAINTS
/

DROP TABLE SERVE_NAV_INPUT_PARAM CASCADE CONSTRAINTS
/

DROP TABLE SERVE_NAV_OUT_PARAM CASCADE CONSTRAINTS
/

DROP TABLE SERVE_OUTPUT_PARAM CASCADE CONSTRAINTS
/

DROP TABLE SERVE_RESULT_EXAMPLE CASCADE CONSTRAINTS
/

DROP TABLE SERVE_SEGMENT CASCADE CONSTRAINTS
/

DROP TABLE SERVE_USER CASCADE CONSTRAINTS
/

DROP TABLE SERVE_USER_AUTHORITY CASCADE CONSTRAINTS
/

DROP TABLE SERVE_VISIT_LOG CASCADE CONSTRAINTS
/

DROP TABLE SERVE_WHITE_LIST CASCADE CONSTRAINTS
/

DROP SEQUENCE SEQ_SERVE_BLACKLIST
/

DROP SEQUENCE SEQ_SERVE_CONFIG
/

DROP SEQUENCE SEQ_SERVE_DATASOURCE
/

DROP SEQUENCE SEQ_SERVE_GIS_DIC_TABLE
/

DROP SEQUENCE SEQ_SERVE_INPARAM
/

DROP SEQUENCE SEQ_SERVE_LOGIN_LOG
/

DROP SEQUENCE SEQ_SERVE_MODIFYLOG
/

DROP SEQUENCE SEQ_SERVE_NAV_INPUT
/

DROP SEQUENCE SEQ_SERVE_NAV_OUT
/

DROP SEQUENCE SEQ_SERVE_NAV_RANGE
/

DROP SEQUENCE SEQ_SERVE_OUTPARAM
/

DROP SEQUENCE SEQ_SERVE_RESULT
/

DROP SEQUENCE SEQ_SERVE_SEGMENT
/

DROP SEQUENCE SEQ_SERVE_USER
/

DROP SEQUENCE SEQ_SERVE_USERAUTH
/

DROP SEQUENCE SEQ_SERVE_WHITELIST
/

CREATE SEQUENCE SEQ_SERVE_BLACKLIST
/

CREATE SEQUENCE SEQ_SERVE_CONFIG
/

CREATE SEQUENCE SEQ_SERVE_DATASOURCE
/

CREATE SEQUENCE SEQ_SERVE_GIS_DIC_TABLE
/

CREATE SEQUENCE SEQ_SERVE_INPARAM
/

CREATE SEQUENCE SEQ_SERVE_LOGIN_LOG
/

CREATE SEQUENCE SEQ_SERVE_MODIFYLOG
/

CREATE SEQUENCE SEQ_SERVE_NAV_INPUT
/

CREATE SEQUENCE SEQ_SERVE_NAV_OUT
/

CREATE SEQUENCE SEQ_SERVE_NAV_RANGE
/

CREATE SEQUENCE SEQ_SERVE_OUTPARAM
/

CREATE SEQUENCE SEQ_SERVE_RESULT
/

CREATE SEQUENCE SEQ_SERVE_SEGMENT
/

CREATE SEQUENCE SEQ_SERVE_USER
/

CREATE SEQUENCE SEQ_SERVE_USERAUTH
/

CREATE SEQUENCE SEQ_SERVE_WHITELIST
/

/*==============================================================*/
/* Table: SERVE_BLACK_LIST                                      */
/*==============================================================*/
CREATE TABLE SERVE_BLACK_LIST 
(
   ID                   NUMBER               NOT NULL,
   IP_ADDRESS           VARCHAR2(32),
   BAN_REASON           VARCHAR2(128),
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_BLACK_LIST PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_BLACK_LIST IS
'服务配置黑名单，此名单内存在的ip地址都不能调用服务接口'
/

COMMENT ON COLUMN SERVE_BLACK_LIST.ID IS
'主键'
/

COMMENT ON COLUMN SERVE_BLACK_LIST.IP_ADDRESS IS
'IP地址表达式'
/

COMMENT ON COLUMN SERVE_BLACK_LIST.BAN_REASON IS
'禁用原因'
/

/*==============================================================*/
/* Table: SERVE_CONFIG                                          */
/*==============================================================*/
CREATE TABLE SERVE_CONFIG 
(
   ID                   NUMBER               NOT NULL,
   PARENT_ID            NUMBER,
   DATA_SOURCE_ID       NUMBER,
   SERVICE_NAME         VARCHAR2(128),
   SERVICE_CODE         VARCHAR2(512),
   SERVICE_TYPE         SMALLINT,
   TREE_LEVEL           SMALLINT,
   IS_SERVICE           SMALLINT,
   QUERY_SQL_CLOB       CLOB,
   QUERY_SQL            VARCHAR2(4000),
   CONFIG_REMARK        VARCHAR2(256),
   RESULT_FORMAT        VARCHAR2(32),
   REQUEST_METHOD       VARCHAR2(32),
   VERIFY_ACCESS        SMALLINT,
   VERIFY_VIEW          SMALLINT,
   VERIFY_IP            SMALLINT,
   REQUEST_EXAMPLE_URL  VARCHAR2(256),
   OTHER_INFO           VARCHAR2(4000),
   PUBLISH_REMARK       VARCHAR2(512),
   CREATE_TIME          TIMESTAMP            DEFAULT SYSTIMESTAMP,
   PUBLISH_TIME         TIMESTAMP,
   SERVICE_STATUS       SMALLINT,
   IS_DELETED           SMALLINT,
   TABLE_NAME           VARCHAR2(32),
   IS_DATE_RANGE        SMALLINT,
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CREATE_USER          VARCHAR(50),
   SERVICE_CODE_EVER    VARCHAR2(512),
   CONSTRAINT PK_SERVE_CONFIG PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_CONFIG IS
'服务配置表，包含配置服务信息，发布信息
'
/

COMMENT ON COLUMN SERVE_CONFIG.ID IS
'主键'
/

COMMENT ON COLUMN SERVE_CONFIG.PARENT_ID IS
'父id'
/

COMMENT ON COLUMN SERVE_CONFIG.DATA_SOURCE_ID IS
'数据源ID'
/

COMMENT ON COLUMN SERVE_CONFIG.SERVICE_NAME IS
'接口名称'
/

COMMENT ON COLUMN SERVE_CONFIG.SERVICE_CODE IS
'服务接口代码或者服务请求地址'
/

COMMENT ON COLUMN SERVE_CONFIG.SERVICE_TYPE IS
'接口类型（3:空间数据，2::外部接口，1:配置接口）'
/

COMMENT ON COLUMN SERVE_CONFIG.TREE_LEVEL IS
'服务树层级（1.2.3.4.5.6.7）默认根节点（服务管理平台作为根节点，需要初始化的时候手动添加）级别0,'
/

COMMENT ON COLUMN SERVE_CONFIG.IS_SERVICE IS
'是否是服务(1：是 0 是目录)'
/

COMMENT ON COLUMN SERVE_CONFIG.QUERY_SQL_CLOB IS
'生成的SQL 超长字段'
/

COMMENT ON COLUMN SERVE_CONFIG.QUERY_SQL IS
'生成的SQL'
/

COMMENT ON COLUMN SERVE_CONFIG.CONFIG_REMARK IS
'服务配置备注'
/

COMMENT ON COLUMN SERVE_CONFIG.RESULT_FORMAT IS
'返回格式(xml/json) 保存格式为 ,xml,json用逗号分隔,注意'
/

COMMENT ON COLUMN SERVE_CONFIG.REQUEST_METHOD IS
'请求方法,保存格式为,get,post,用逗号分隔,注意'
/

COMMENT ON COLUMN SERVE_CONFIG.VERIFY_ACCESS IS
'是否验证访问'
/

COMMENT ON COLUMN SERVE_CONFIG.VERIFY_VIEW IS
'是否验证查看'
/

COMMENT ON COLUMN SERVE_CONFIG.VERIFY_IP IS
'是否验证IP'
/

COMMENT ON COLUMN SERVE_CONFIG.REQUEST_EXAMPLE_URL IS
'请求示例url'
/

COMMENT ON COLUMN SERVE_CONFIG.OTHER_INFO IS
'其他信息'
/

COMMENT ON COLUMN SERVE_CONFIG.PUBLISH_REMARK IS
'发布的备注'
/

COMMENT ON COLUMN SERVE_CONFIG.CREATE_TIME IS
'创建时间'
/

COMMENT ON COLUMN SERVE_CONFIG.PUBLISH_TIME IS
'发布时间'
/

COMMENT ON COLUMN SERVE_CONFIG.SERVICE_STATUS IS
'接口状态（1不可用，2未发布，3已发布,4 待审批,5 已驳回）'
/

COMMENT ON COLUMN SERVE_CONFIG.IS_DELETED IS
'是否删除(1：是，0：否)'
/

COMMENT ON COLUMN SERVE_CONFIG.TABLE_NAME IS
'配置表名,通过此字段也能够判断是否是通过配置向导生成的服务配置'
/

COMMENT ON COLUMN SERVE_CONFIG.IS_DATE_RANGE IS
'是否包含时间区间(1：是，0：否)'
/

COMMENT ON COLUMN SERVE_CONFIG.CREATE_USER IS
'服务创建者'
/

COMMENT ON COLUMN SERVE_CONFIG.SERVICE_CODE_EVER IS
'删除前的服务代码'
/

/*==============================================================*/
/* Index: UK_SERVICE_CODE                                       */
/*==============================================================*/
CREATE UNIQUE INDEX UK_SERVICE_CODE ON SERVE_CONFIG (
   SERVICE_CODE ASC
)
/

/*==============================================================*/
/* Table: SERVE_DATA_SOURCE                                     */
/*==============================================================*/
CREATE TABLE SERVE_DATA_SOURCE 
(
   ID                   NUMBER               NOT NULL,
   SOURCE_NAME          VARCHAR2(64),
   SOURCE_TYPE          SMALLINT,
   SOURCE_CLASS         VARCHAR2(128),
   SOURCE_URL           VARCHAR2(128),
   SOURCE_USER          VARCHAR2(128),
   SOURCE_PASS          VARCHAR2(128),
   SOURCE_REMARK        VARCHAR2(256),
   IS_DELETED           SMALLINT,
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_DATA_SOURCE PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_DATA_SOURCE IS
'数据源管理配置信息表,此表假删，删除前需要判断是否有状态正常的服务引用
动态数据源key（datasource_id）'
/

COMMENT ON COLUMN SERVE_DATA_SOURCE.ID IS
'主键'
/

COMMENT ON COLUMN SERVE_DATA_SOURCE.SOURCE_NAME IS
'数据源名称'
/

COMMENT ON COLUMN SERVE_DATA_SOURCE.SOURCE_TYPE IS
'数据库类型(1:oracle,2:sqlserver,3:mysql)'
/

COMMENT ON COLUMN SERVE_DATA_SOURCE.SOURCE_CLASS IS
'数据库驱动，根据数据库类型生成驱动className'
/

COMMENT ON COLUMN SERVE_DATA_SOURCE.SOURCE_URL IS
'数据库连接地址'
/

COMMENT ON COLUMN SERVE_DATA_SOURCE.SOURCE_USER IS
'数据库登录名'
/

COMMENT ON COLUMN SERVE_DATA_SOURCE.SOURCE_PASS IS
'数据库密码'
/

COMMENT ON COLUMN SERVE_DATA_SOURCE.SOURCE_REMARK IS
'数据库备注'
/

COMMENT ON COLUMN SERVE_DATA_SOURCE.IS_DELETED IS
'是否删除'
/

/*==============================================================*/
/* Table: SERVE_EXTEND_CONFIG                                   */
/*==============================================================*/
CREATE TABLE SERVE_EXTEND_CONFIG 
(
   ID                   NUMBER               NOT NULL,
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_EXTEND_CONFIG PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_EXTEND_CONFIG IS
'服务配置信息扩展'
/

COMMENT ON COLUMN SERVE_EXTEND_CONFIG.ID IS
'主键（与config表一对一的关系）'
/

/*==============================================================*/
/* Table: SERVE_EXTEND_REGISTER                                 */
/*==============================================================*/
CREATE TABLE SERVE_EXTEND_REGISTER 
(
   ID                   NUMBER               NOT NULL,
   UPDATATIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_EXTEND_REGISTER PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_EXTEND_REGISTER IS
'服务注册扩展信息'
/

COMMENT ON COLUMN SERVE_EXTEND_REGISTER.ID IS
'主键'
/

/*==============================================================*/
/* Table: SERVE_EXTEND_SPACE                                    */
/*==============================================================*/
CREATE TABLE SERVE_EXTEND_SPACE 
(
   ID                   NUMBER               NOT NULL,
   URL_TEST             VARCHAR2(1024),
   URL_FORMAL           VARCHAR2(1024),
   GE0_FEATURES         SMALLINT,
   MAX_LENGTH           NUMBER,
   IS_VISIBLE           SMALLINT,
   LAYER_TYPE           SMALLINT,
   DISPLAY_SCALE_MAX    NUMBER,
   DISPLAY_SCALE_MIN    NUMBER,
   IS_LABEL             SMALLINT,
   GIS_SERVER           SMALLINT,
   COORDINATE_SYSTEM    VARCHAR2(32),
   COORDINATE_CODE      VARCHAR2(32),
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_EXTEND_SPACE PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_EXTEND_SPACE IS
'空间服务配置信息表'
/

COMMENT ON COLUMN SERVE_EXTEND_SPACE.ID IS
'主键'
/

COMMENT ON COLUMN SERVE_EXTEND_SPACE.URL_TEST IS
'测试地址'
/

COMMENT ON COLUMN SERVE_EXTEND_SPACE.URL_FORMAL IS
'正式地址'
/

COMMENT ON COLUMN SERVE_EXTEND_SPACE.GE0_FEATURES IS
'几何特征(1点，2线，3面，4null)'
/

COMMENT ON COLUMN SERVE_EXTEND_SPACE.MAX_LENGTH IS
'最大记录数'
/

COMMENT ON COLUMN SERVE_EXTEND_SPACE.IS_VISIBLE IS
'图层可见性(1:可见，0 不可见)'
/

COMMENT ON COLUMN SERVE_EXTEND_SPACE.LAYER_TYPE IS
'图层类型(1:FEATURE,2:RASTER)'
/

COMMENT ON COLUMN SERVE_EXTEND_SPACE.DISPLAY_SCALE_MAX IS
'最大显示比例'
/

COMMENT ON COLUMN SERVE_EXTEND_SPACE.DISPLAY_SCALE_MIN IS
'最小显示比例'
/

COMMENT ON COLUMN SERVE_EXTEND_SPACE.IS_LABEL IS
'是否有标志(1:是,0:否)'
/

COMMENT ON COLUMN SERVE_EXTEND_SPACE.GIS_SERVER IS
'ARCGIS_SERVER 数据字典(1:M+K,2:M+K+WCS,3:M+K+MDB,4:M+K+WMS,5:M+K+WFS,6:M+K+FA.7:M+K+S,8:M+K+NA])'
/

COMMENT ON COLUMN SERVE_EXTEND_SPACE.COORDINATE_SYSTEM IS
'坐标系统:eg:gcs_wgs_1984'
/

COMMENT ON COLUMN SERVE_EXTEND_SPACE.COORDINATE_CODE IS
'坐标代码:eg:4326'
/

/*==============================================================*/
/* Table: SERVE_GIS_DICTIONARY                                  */
/*==============================================================*/
CREATE TABLE SERVE_GIS_DICTIONARY 
(
   ID                   NUMBER               NOT NULL,
   CONFIG_ID            NUMBER,
   FILED_NAME           VARCHAR(100),
   FILED_TYPE           VARCHAR(20),
   FILED_LENGTH         NUMBER,
   DECIMAL_LENGTH       NUMBER,
   IS_NULL              VARCHAR(10),
   FILED_DESC           VARCHAR(1024),
   FIELD_REMARK         VARCHAR(1024),
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_GIS_DICTIONARY PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_GIS_DICTIONARY IS
'GIS空间服务的数据字典'
/

COMMENT ON COLUMN SERVE_GIS_DICTIONARY.CONFIG_ID IS
'外键ID'
/

COMMENT ON COLUMN SERVE_GIS_DICTIONARY.FILED_NAME IS
'字段名称'
/

COMMENT ON COLUMN SERVE_GIS_DICTIONARY.FILED_TYPE IS
'字段类型Double,Float,Int,Long,String,Date'
/

COMMENT ON COLUMN SERVE_GIS_DICTIONARY.FILED_LENGTH IS
'字段长度'
/

COMMENT ON COLUMN SERVE_GIS_DICTIONARY.DECIMAL_LENGTH IS
'小数位数'
/

COMMENT ON COLUMN SERVE_GIS_DICTIONARY.IS_NULL IS
'是否为空 YES NO'
/

COMMENT ON COLUMN SERVE_GIS_DICTIONARY.FILED_DESC IS
'字段说明'
/

COMMENT ON COLUMN SERVE_GIS_DICTIONARY.FIELD_REMARK IS
'备注'
/

/*==============================================================*/
/* Table: SERVE_INPUT_PARAM                                     */
/*==============================================================*/
CREATE TABLE SERVE_INPUT_PARAM 
(
   ID                   NUMBER               NOT NULL,
   SERVICE_ID           NUMBER,
   PARAM_CODE           VARCHAR2(32),
   PARAM_DESC           VARCHAR2(512),
   PARAM_TYPE           SMALLINT,
   IS_REQUIRED          SMALLINT,
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_INPUT_PARAM PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_INPUT_PARAM IS
'输入参数信息配置表'
/

COMMENT ON COLUMN SERVE_INPUT_PARAM.PARAM_CODE IS
'参数名'
/

COMMENT ON COLUMN SERVE_INPUT_PARAM.PARAM_DESC IS
'参数描述'
/

COMMENT ON COLUMN SERVE_INPUT_PARAM.PARAM_TYPE IS
'列类型0:int,1:string,2:float,3:date,4:datetime, 时间格式为标准格式（yyyy-MM-dd HH:mm:ss)'
/

COMMENT ON COLUMN SERVE_INPUT_PARAM.IS_REQUIRED IS
'是否必填(0:否 1:是)'
/

/*==============================================================*/
/* Table: SERVE_LOGIN_LOG                                       */
/*==============================================================*/
CREATE TABLE SERVE_LOGIN_LOG 
(
   ID                   NUMBER               NOT NULL,
   USER_NAME            VARCHAR(128),
   TOKEN_ID             VARCHAR(128),
   IP                   VARCHAR(128),
   REMARK               VARCHAR(4000),
   IS_SUCC              SMALLINT,
   LOGIN_TIME           TIMESTAMP,
   UPDATE_TIME          TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_LOGIN_LOG PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_LOGIN_LOG IS
'服务登录日志记录
同时用来存储注册与登陆时生成的验证码'
/

COMMENT ON COLUMN SERVE_LOGIN_LOG.USER_NAME IS
'用户名'
/

COMMENT ON COLUMN SERVE_LOGIN_LOG.TOKEN_ID IS
'用户访问令牌'
/

COMMENT ON COLUMN SERVE_LOGIN_LOG.IP IS
'访问IP'
/

COMMENT ON COLUMN SERVE_LOGIN_LOG.REMARK IS
'登录信息'
/

COMMENT ON COLUMN SERVE_LOGIN_LOG.IS_SUCC IS
'是否登陆成功 1:成功 0：不成功'
/

COMMENT ON COLUMN SERVE_LOGIN_LOG.LOGIN_TIME IS
'登陆时间'
/

/*==============================================================*/
/* Table: SERVE_MODIFY_LOG                                      */
/*==============================================================*/
CREATE TABLE SERVE_MODIFY_LOG 
(
   ID                   NUMBER               NOT NULL,
   SERVICE_ID           NUMBER,
   OPERATOR_ID          NUMBER,
   OPERATOR_NAME        VARCHAR2(128),
   MODIFY_REMARK        VARCHAR2(4000),
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_MODIFY_LOG PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_MODIFY_LOG IS
'服务修改记录日志表'
/

COMMENT ON COLUMN SERVE_MODIFY_LOG.ID IS
'主键'
/

COMMENT ON COLUMN SERVE_MODIFY_LOG.SERVICE_ID IS
'服务ID'
/

/*==============================================================*/
/* Table: SERVE_NAV_DATE_RANGE                                  */
/*==============================================================*/
CREATE TABLE SERVE_NAV_DATE_RANGE 
(
   ID                   NUMBER               NOT NULL,
   SERVICE_ID           NUMBER,
   COLUMN_CODE          VARCHAR2(32),
   DATE_RANGE           NUMBER,
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_NAV_DATE_RANGE PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON COLUMN SERVE_NAV_DATE_RANGE.COLUMN_CODE IS
'列名'
/

COMMENT ON COLUMN SERVE_NAV_DATE_RANGE.DATE_RANGE IS
'近xx天 '
/

/*==============================================================*/
/* Table: SERVE_NAV_INPUT_PARAM                                 */
/*==============================================================*/
CREATE TABLE SERVE_NAV_INPUT_PARAM 
(
   ID                   NUMBER               NOT NULL,
   SERVICE_ID           NUMBER,
   COLUMN_CODE          VARCHAR2(32),
   PARAM_CODE           VARCHAR2(32),
   COLUMN_DESC          VARCHAR2(256),
   IS_REQUIRED          SMALLINT,
   OPERATOR             VARCHAR2(32),
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_NAV_INPUT_PARAM PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON COLUMN SERVE_NAV_INPUT_PARAM.ID IS
'主键ID'
/

COMMENT ON COLUMN SERVE_NAV_INPUT_PARAM.SERVICE_ID IS
'服务id'
/

COMMENT ON COLUMN SERVE_NAV_INPUT_PARAM.COLUMN_CODE IS
'表列名'
/

COMMENT ON COLUMN SERVE_NAV_INPUT_PARAM.PARAM_CODE IS
'参数名'
/

COMMENT ON COLUMN SERVE_NAV_INPUT_PARAM.COLUMN_DESC IS
'列描述'
/

COMMENT ON COLUMN SERVE_NAV_INPUT_PARAM.IS_REQUIRED IS
'是否必须(1:是,0:否)'
/

COMMENT ON COLUMN SERVE_NAV_INPUT_PARAM.OPERATOR IS
'运算符'
/

/*==============================================================*/
/* Table: SERVE_NAV_OUT_PARAM                                   */
/*==============================================================*/
CREATE TABLE SERVE_NAV_OUT_PARAM 
(
   ID                   NUMBER               NOT NULL,
   SERVICE_ID           NUMBER,
   COLUMN_CODE          VARCHAR2(32),
   COLUMN_DESC          VARCHAR2(256),
   COLUMN_ALIAS         VARCHAR2(32),
   SORT_ORDER           VARCHAR2(32),
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_NAV_OUT_PARAM PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON COLUMN SERVE_NAV_OUT_PARAM.SERVICE_ID IS
'服务id'
/

COMMENT ON COLUMN SERVE_NAV_OUT_PARAM.COLUMN_CODE IS
'字段名'
/

COMMENT ON COLUMN SERVE_NAV_OUT_PARAM.COLUMN_DESC IS
'字段备注'
/

COMMENT ON COLUMN SERVE_NAV_OUT_PARAM.COLUMN_ALIAS IS
'参数别名'
/

COMMENT ON COLUMN SERVE_NAV_OUT_PARAM.SORT_ORDER IS
'排序类型(desc asc)'
/

/*==============================================================*/
/* Table: SERVE_OUTPUT_PARAM                                    */
/*==============================================================*/
CREATE TABLE SERVE_OUTPUT_PARAM 
(
   ID                   NUMBER               NOT NULL,
   SERVICE_ID           NUMBER,
   PARAM_CODE           VARCHAR2(128),
   PARAM_DESC           VARCHAR2(128),
   PARAM_TYPE           SMALLINT,
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_OUTPUT_PARAM PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_OUTPUT_PARAM IS
'输出字段信息配置表
'
/

COMMENT ON COLUMN SERVE_OUTPUT_PARAM.ID IS
'自增id'
/

COMMENT ON COLUMN SERVE_OUTPUT_PARAM.SERVICE_ID IS
'配置表id'
/

COMMENT ON COLUMN SERVE_OUTPUT_PARAM.PARAM_CODE IS
'列名'
/

COMMENT ON COLUMN SERVE_OUTPUT_PARAM.PARAM_DESC IS
'列描述'
/

COMMENT ON COLUMN SERVE_OUTPUT_PARAM.PARAM_TYPE IS
'列类型0:int,1:string,2:float,3:date,4:datetime, 时间格式为标准格式（yyyy-MM-dd HH:mm:ss)'
/

COMMENT ON COLUMN SERVE_OUTPUT_PARAM.UPDATETIME IS
'时间戳'
/

/*==============================================================*/
/* Table: SERVE_RESULT_EXAMPLE                                  */
/*==============================================================*/
CREATE TABLE SERVE_RESULT_EXAMPLE 
(
   ID                   NUMBER               NOT NULL,
   SERVICE_ID           NUMBER,
   RESULT_TYPE          VARCHAR2(50),
   RESULT_EXAMPLE       CLOB,
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_RESULT_EXAMPLE PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_RESULT_EXAMPLE IS
'返回接口示例信息表，保存各种类型的返回信息'
/

COMMENT ON COLUMN SERVE_RESULT_EXAMPLE.ID IS
'主键'
/

COMMENT ON COLUMN SERVE_RESULT_EXAMPLE.SERVICE_ID IS
'主服务ID'
/

COMMENT ON COLUMN SERVE_RESULT_EXAMPLE.RESULT_TYPE IS
'返回类型(xml/jsom/...)'
/

COMMENT ON COLUMN SERVE_RESULT_EXAMPLE.RESULT_EXAMPLE IS
'返回结果 实例'
/

COMMENT ON COLUMN SERVE_RESULT_EXAMPLE.UPDATETIME IS
'更新时间'
/

/*==============================================================*/
/* Table: SERVE_SEGMENT                                         */
/*==============================================================*/
CREATE TABLE SERVE_SEGMENT 
(
   ID                   NUMBER               NOT NULL,
   SERVICE_ID           NUMBER,
   SEGMENT_CODE         VARCHAR2(128),
   PARAM_CODE           VARCHAR2(32),
   REPLACE_SQL          VARCHAR2(1024),
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_SEGMENT PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_SEGMENT IS
'片段信息配置表：
根据service_id和param_code 关联'
/

COMMENT ON COLUMN SERVE_SEGMENT.SERVICE_ID IS
'主表ID'
/

COMMENT ON COLUMN SERVE_SEGMENT.SEGMENT_CODE IS
'片段代码'
/

COMMENT ON COLUMN SERVE_SEGMENT.PARAM_CODE IS
'入参代码'
/

COMMENT ON COLUMN SERVE_SEGMENT.REPLACE_SQL IS
'实际需要替换的SQL'
/

/*==============================================================*/
/* Table: SERVE_USER                                            */
/*==============================================================*/
CREATE TABLE SERVE_USER 
(
   ID                   NUMBER               NOT NULL,
   USER_NAME            VARCHAR2(128),
   USER_NAME_EVER       VARCHAR2(128),
   NICK_NAME            VARCHAR2(128),
   USER_PASS            VARCHAR2(1024),
   ENCRYPT_SALT         VARCHAR2(64),
   APP_KEY              VARCHAR2(64),
   USER_EMAIL           VARCHAR2(64),
   USER_MOBILE          VARCHAR2(32),
   USER_CHANNEL         SMALLINT,
   USER_REMARK          VARCHAR2(512),
   IS_DELETED           SMALLINT,
   CREATE_TIME          TIMESTAMP,
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_USER PRIMARY KEY (ID),
   CONSTRAINT AK_UNIQUE_USER_SERVE_US UNIQUE (USER_NAME)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_USER IS
'服务配置用户表，管理接口访问和查看接口的用户信息'
/

COMMENT ON COLUMN SERVE_USER.ID IS
'主键'
/

COMMENT ON COLUMN SERVE_USER.USER_NAME IS
'用户名'
/

COMMENT ON COLUMN SERVE_USER.USER_NAME_EVER IS
'删除前的用户名'
/

COMMENT ON COLUMN SERVE_USER.NICK_NAME IS
'昵称'
/

COMMENT ON COLUMN SERVE_USER.USER_PASS IS
'密码'
/

COMMENT ON COLUMN SERVE_USER.ENCRYPT_SALT IS
'加密因子'
/

COMMENT ON COLUMN SERVE_USER.APP_KEY IS
'接口key'
/

COMMENT ON COLUMN SERVE_USER.USER_EMAIL IS
'邮箱'
/

COMMENT ON COLUMN SERVE_USER.USER_MOBILE IS
'手机号'
/

COMMENT ON COLUMN SERVE_USER.USER_CHANNEL IS
'网络标识（1.内网 ,2:外网）'
/

COMMENT ON COLUMN SERVE_USER.USER_REMARK IS
'备注'
/

COMMENT ON COLUMN SERVE_USER.IS_DELETED IS
'是否已删除（1:是,0:否）'
/

COMMENT ON COLUMN SERVE_USER.CREATE_TIME IS
'创建时间'
/

COMMENT ON COLUMN SERVE_USER.UPDATETIME IS
'更新时间'
/

/*==============================================================*/
/* Table: SERVE_USER_AUTHORITY                                  */
/*==============================================================*/
CREATE TABLE SERVE_USER_AUTHORITY 
(
   ID                   NUMBER               NOT NULL,
   USER_ID              NUMBER,
   SERVICE_ID           NUMBER,
   VIEW_PERMISSION      SMALLINT,
   ACCESS_PERMISSION    SMALLINT,
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_USER_AUTHORITY PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_USER_AUTHORITY IS
'用户接口服务权限表，包含访问权限和查看权限'
/

COMMENT ON COLUMN SERVE_USER_AUTHORITY.ID IS
'主键'
/

COMMENT ON COLUMN SERVE_USER_AUTHORITY.USER_ID IS
'用户id'
/

COMMENT ON COLUMN SERVE_USER_AUTHORITY.SERVICE_ID IS
'服务id'
/

COMMENT ON COLUMN SERVE_USER_AUTHORITY.VIEW_PERMISSION IS
'是否能查看（1：能，0:否）'
/

COMMENT ON COLUMN SERVE_USER_AUTHORITY.ACCESS_PERMISSION IS
'是否能访问（1：能，0:否）'
/

/*==============================================================*/
/* Table: SERVE_VISIT_LOG                                       */
/*==============================================================*/
CREATE TABLE SERVE_VISIT_LOG 
(
   ID                   NUMBER               NOT NULL,
   USER_ID              NUMBER,
   USER_NAME            VARCHAR2(128),
   USER_CHANNEL         SMALLINT,
   SERVICE_ID           NUMBER,
   SERVICE_TYPE         SMALLINT,
   SERVICE_NAME         VARCHAR2(128),
   LOG_PARAMS           VARCHAR2(1024),
   RET_CODE             VARCHAR2(10),
   RET_MSG              VARCHAR2(512),
   COST_TIME            NUMBER,
   VISIT_IPADDRESS      VARCHAR2(32),
   START_TIME           TIMESTAMP,
   END_TIME             TIMESTAMP,
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   VISIT_DATE           NUMBER,
   CONSTRAINT PK_SERVE_VISIT_LOG PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_VISIT_LOG IS
'接口访问日志信息表'
/

COMMENT ON COLUMN SERVE_VISIT_LOG.USER_NAME IS
'用户name（冗余字段）'
/

COMMENT ON COLUMN SERVE_VISIT_LOG.USER_CHANNEL IS
'内外网标识(1:内网,2:外网)'
/

COMMENT ON COLUMN SERVE_VISIT_LOG.SERVICE_TYPE IS
'接口类型（3:空间数据，2::外部接口，1:配置接口）'
/

COMMENT ON COLUMN SERVE_VISIT_LOG.SERVICE_NAME IS
'服务名称（冗余字段）'
/

COMMENT ON COLUMN SERVE_VISIT_LOG.LOG_PARAMS IS
'访问参数'
/

COMMENT ON COLUMN SERVE_VISIT_LOG.RET_CODE IS
'返回代码'
/

COMMENT ON COLUMN SERVE_VISIT_LOG.RET_MSG IS
'日志信息'
/

COMMENT ON COLUMN SERVE_VISIT_LOG.COST_TIME IS
'耗时(单位MS)'
/

COMMENT ON COLUMN SERVE_VISIT_LOG.VISIT_IPADDRESS IS
'访问ip'
/

COMMENT ON COLUMN SERVE_VISIT_LOG.START_TIME IS
'访问时间'
/

COMMENT ON COLUMN SERVE_VISIT_LOG.END_TIME IS
'访问结束时间'
/

/*==============================================================*/
/* Table: SERVE_WHITE_LIST                                      */
/*==============================================================*/
CREATE TABLE SERVE_WHITE_LIST 
(
   ID                   NUMBER               NOT NULL,
   SERVICE_ID           NUMBER,
   IP_ADDRESS           VARCHAR2(16),
   UPDATETIME           TIMESTAMP            DEFAULT SYSTIMESTAMP,
   CONSTRAINT PK_SERVE_WHITE_LIST PRIMARY KEY (ID)
)
ROWDEPENDENCIES
/

COMMENT ON TABLE SERVE_WHITE_LIST IS
'服务配置白名单信息，当接口调用需要验证IP时，才需要配置'
/

COMMENT ON COLUMN SERVE_WHITE_LIST.ID IS
'主键'
/

COMMENT ON COLUMN SERVE_WHITE_LIST.SERVICE_ID IS
'主服务ID'
/

COMMENT ON COLUMN SERVE_WHITE_LIST.IP_ADDRESS IS
'IP地址'
/

ALTER TABLE SERVE_CONFIG
   ADD CONSTRAINT FK_SERVE_CO_REFERENCE_SERVE_DA FOREIGN KEY (DATA_SOURCE_ID)
      REFERENCES SERVE_DATA_SOURCE (ID)
/

ALTER TABLE SERVE_CONFIG
   ADD CONSTRAINT FK_SERVE_CO_REFERENCE_SERVE_CO FOREIGN KEY (PARENT_ID)
      REFERENCES SERVE_CONFIG (ID)
/

ALTER TABLE SERVE_EXTEND_CONFIG
   ADD CONSTRAINT FK_SERVE_CONFIG_REFERENCE FOREIGN KEY (ID)
      REFERENCES SERVE_CONFIG (ID)
/

ALTER TABLE SERVE_EXTEND_REGISTER
   ADD CONSTRAINT FK_SERVE_REGISTER_REFERENCE FOREIGN KEY (ID)
      REFERENCES SERVE_CONFIG (ID)
/

ALTER TABLE SERVE_EXTEND_SPACE
   ADD CONSTRAINT FK_SERVE_SPACE_REFERENCE FOREIGN KEY (ID)
      REFERENCES SERVE_CONFIG (ID)
/

ALTER TABLE SERVE_GIS_DICTIONARY
   ADD CONSTRAINT FK_SERVE_GI_REFERENCE_SERVE_EX FOREIGN KEY (CONFIG_ID)
      REFERENCES SERVE_EXTEND_SPACE (ID)
/

ALTER TABLE SERVE_INPUT_PARAM
   ADD CONSTRAINT FK_SERVE_IN_REFERENCE_SERVE_CO FOREIGN KEY (SERVICE_ID)
      REFERENCES SERVE_CONFIG (ID)
/

ALTER TABLE SERVE_MODIFY_LOG
   ADD CONSTRAINT FK_SERVE_MO_REFERENCE_SERVE_CO FOREIGN KEY (SERVICE_ID)
      REFERENCES SERVE_CONFIG (ID)
/

ALTER TABLE SERVE_NAV_DATE_RANGE
   ADD CONSTRAINT FK_SERVE_NA_RANGE FOREIGN KEY (SERVICE_ID)
      REFERENCES SERVE_CONFIG (ID)
/

ALTER TABLE SERVE_NAV_INPUT_PARAM
   ADD CONSTRAINT FK_SERVE_NA_INPUT FOREIGN KEY (SERVICE_ID)
      REFERENCES SERVE_CONFIG (ID)
/

ALTER TABLE SERVE_NAV_OUT_PARAM
   ADD CONSTRAINT FK_SERVE_NA_OUTPUT FOREIGN KEY (SERVICE_ID)
      REFERENCES SERVE_CONFIG (ID)
/

ALTER TABLE SERVE_OUTPUT_PARAM
   ADD CONSTRAINT FK_SERVE_OU_REFERENCE_SERVE_CO FOREIGN KEY (SERVICE_ID)
      REFERENCES SERVE_CONFIG (ID)
/

ALTER TABLE SERVE_RESULT_EXAMPLE
   ADD CONSTRAINT FK_SERVE_RE_REFERENCE_SERVE_CO FOREIGN KEY (SERVICE_ID)
      REFERENCES SERVE_CONFIG (ID)
/

ALTER TABLE SERVE_SEGMENT
   ADD CONSTRAINT FK_SERVE_SE_REFERENCE_SERVE_CO FOREIGN KEY (SERVICE_ID)
      REFERENCES SERVE_CONFIG (ID)
/

ALTER TABLE SERVE_USER_AUTHORITY
   ADD CONSTRAINT FK_SERVE_US_REFERENCE_SERVE_CO FOREIGN KEY (SERVICE_ID)
      REFERENCES SERVE_CONFIG (ID)
/

ALTER TABLE SERVE_USER_AUTHORITY
   ADD CONSTRAINT FK_SERVE_US_REFERENCE_SERVE_US FOREIGN KEY (USER_ID)
      REFERENCES SERVE_USER (ID)
/

ALTER TABLE SERVE_WHITE_LIST
   ADD CONSTRAINT FK_SERVE_WH_REFERENCE_SERVE_CO FOREIGN KEY (SERVICE_ID)
      REFERENCES SERVE_CONFIG (ID)
/


CREATE TRIGGER TIB_SERVE_BLACK_LIST BEFORE INSERT
ON SERVE_BLACK_LIST FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_BLACKLIST
    SELECT SEQ_SERVE_BLACKLIST.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_CONFIG BEFORE INSERT
ON SERVE_CONFIG FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_CONFIG
    SELECT SEQ_SERVE_CONFIG.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_DATA_SOURCE BEFORE INSERT
ON SERVE_DATA_SOURCE FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_DATASOURCE
    SELECT SEQ_SERVE_DATASOURCE.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_GIS_DICTIONARY BEFORE INSERT
ON SERVE_GIS_DICTIONARY FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_GIS_DIC_TABLE
    SELECT SEQ_SERVE_GIS_DIC_TABLE.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_INPUT_PARAM BEFORE INSERT
ON SERVE_INPUT_PARAM FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_INPARAM
    SELECT SEQ_SERVE_INPARAM.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_LOGIN_LOG BEFORE INSERT
ON SERVE_LOGIN_LOG FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_LOGIN_LOG
    SELECT SEQ_SERVE_LOGIN_LOG.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_MODIFY_LOG BEFORE INSERT
ON SERVE_MODIFY_LOG FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_MODIFYLOG
    SELECT SEQ_SERVE_MODIFYLOG.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_NAV_DATE_RANGE BEFORE INSERT
ON SERVE_NAV_DATE_RANGE FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_NAV_RANGE
    SELECT SEQ_SERVE_NAV_RANGE.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_NAV_INPUT_PARAM BEFORE INSERT
ON SERVE_NAV_INPUT_PARAM FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_NAV_INPUT
    SELECT SEQ_SERVE_NAV_INPUT.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_NAV_OUT_PARAM BEFORE INSERT
ON SERVE_NAV_OUT_PARAM FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_NAV_OUT
    SELECT SEQ_SERVE_NAV_OUT.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_OUTPUT_PARAM BEFORE INSERT
ON SERVE_OUTPUT_PARAM FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_OUTPARAM
    SELECT SEQ_SERVE_OUTPARAM.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_RESULT_EXAMPLE BEFORE INSERT
ON SERVE_RESULT_EXAMPLE FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_RESULT
    SELECT SEQ_SERVE_RESULT.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_SEGMENT BEFORE INSERT
ON SERVE_SEGMENT FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_SEGMENT
    SELECT SEQ_SERVE_SEGMENT.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_USER BEFORE INSERT
ON SERVE_USER FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_USER
    SELECT SEQ_SERVE_USER.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_USER_AUTHORITY BEFORE INSERT
ON SERVE_USER_AUTHORITY FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_USERAUTH
    SELECT SEQ_SERVE_USERAUTH.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/


CREATE TRIGGER TIB_SERVE_WHITE_LIST BEFORE INSERT
ON SERVE_WHITE_LIST FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE SEQ_SERVE_WHITELIST
    SELECT SEQ_SERVE_WHITELIST.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
/
